package application.dao;

import application.dao.entity.*;

import java.util.Date;
import java.util.List;

public class DBService {
    /**
     * 新增用户
     *
     * @param user
     * @return
     */
    public static boolean addUser(User user) {
        String sql = "INSERT INTO `registered_users`(" +
                "`userName`, `passWord`, `resetCaptcha`, `realName`, `sex`, `idCard`, `telNumber`, `homeAddress`, `actApproval`, `isForbidden`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        int update = AccessDB.update(sql, user.getUserName(), user.getPassWord(), user.getResetCaptcha(), user.getRealName()
                , user.getSex(), user.getIdCard(), user.getTelNumber(), user.getHomeAddress(), user.getActApproval(), user.getIsForbidden()
        );
        return update == 1;
    }

    /**
     * 更新用户
     *
     * @param user
     * @return
     */
    public static boolean updateUser(User user) {
        String sql = "UPDATE  `registered_users` SET `userName` = ?, `passWord` = ?, `resetCaptcha` = ?," +
                " `realName` = ?, `sex` = ?, `idCard` = ?, `telNumber` = ?, `homeAddress` = ?, `actApproval` = ?, " +
                "`isForbidden` = ? WHERE `id` =?";
        int update = AccessDB.update(sql, user.getUserName(), user.getPassWord(), user.getResetCaptcha(), user.getRealName()
                , user.getSex(), user.getIdCard(), user.getTelNumber(), user.getHomeAddress(), user.getActApproval(), user.getIsForbidden()
                , user.getID());
        return update == 1;
    }

    /**
     * 运动员登录
     */
    public static User loginUser(String userName, String passWord) {
        return loginUser(userName, passWord, "registered_users", User.class);
    }

    /**
     * 管理员登录
     */
    public static Manager loginAdmin(String userName, String passWord) {
        return loginUser(userName, passWord, "registered_managers", Manager.class);
    }

    /**
     * 登录
     *
     * @param userName
     * @param passWord
     * @param table
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> T loginUser(String userName, String passWord, String table, Class<T> clazz) {
        String sql = "select * from  `" + table + "` where 1=1 and userName=? and passWord=?";
        return AccessDB.queryOne(sql, clazz, userName, passWord);
    }

    /**
     * 注册管理员
     *
     * @param user
     * @return
     */
    public static boolean addManager(Manager user) {
        String sql = "INSERT INTO `registered_managers`" +
                "( `userName`, `passWord`, `realName`, `sex`, `idCard`, `telNumber`, `homeAddress`)" +
                " VALUES ( ?, ?, ?, ?, ?, ?, ?)";
        int update = AccessDB.update(sql, user.getUserName(), user.getPassWord(), user.getRealName(),
                user.getSex(), user.getIdCard(), user.getTelNumber(), user.getHomeAddress()
        );
        return update == 1;
    }

    /**
     * 更新管理员信息
     *
     * @param user
     * @return
     */
    public static boolean updateManager(Manager user) {
        String sql = "UPDATE `registered_managers` SET `userName` = ?, `passWord` =?, " +
                "`realName` = ?, `sex` = ?, `idCard` = ?, `telNumber` = ?, `homeAddress` = ? WHERE `ID` = ?";
        int update = AccessDB.update(sql, user.getUserName(), user.getPassWord(), user.getRealName(),
                user.getSex(), user.getIdCard(), user.getTelNumber(), user.getHomeAddress(), user.getID()
        );
        return update == 1;
    }

    /**
     * 发布公告
     *
     * @param data
     * @return
     */
    public static boolean addNotice(Notice data) {
        String sql = "INSERT INTO `anouncement`(`anmTitle`, `anmText`, `pubTime`) VALUES (?,?,?)";
        int update = AccessDB.update(sql, data.getAnmTitle(), data.getAnmText(), new Date());
        return update == 1;
    }

    /**
     * 发布新闻
     *
     * @param data
     * @return
     */
    public static boolean addNews(News data) {
        String sql = "INSERT INTO `news`(`newsTitle`, `newsText`, `pubTime`) VALUES (?,?,?)";
        int update = AccessDB.update(sql, data.getNewsTitle(), data.getNewsText(), new Date());
        return update == 1;
    }

    /**
     * 查用户比赛成绩
     *
     * @param userNameText
     * @param realNameText
     * @return
     */
    public static CompetitionResult queryScore(String userNameText, String realNameText) {
        StringBuilder stringBuilder = new StringBuilder("select * from competition_results where 1=1 ");
        if (notBlank(userNameText)) {
            stringBuilder.append("  and userName=" + userNameText);
        }
        if (notBlank(realNameText)) {
            stringBuilder.append("  and realName=" + realNameText);
        }
        stringBuilder.append(" limit 1");
        CompetitionResult competitionResult = AccessDB.queryOne(stringBuilder.toString(), CompetitionResult.class, userNameText, realNameText);
        return competitionResult;
    }

    /**
     * 查所有比赛成绩
     *
     * @return
     */
    public static List<CompetitionResult> queryAllScore() {
        String sql = "select * from competition_results order by `rank` asc";
        List<CompetitionResult> competitionResults = AccessDB.queryList(sql, CompetitionResult.class);
        return competitionResults;
    }

    /**
     * 审批注册用户
     *
     * @param id
     * @param actApproval
     * @return
     */
    public static Boolean auditUser(Integer id, Integer actApproval) {
        String sql = "UPDATE  `registered_users`  set `actApproval`=? where id=?";
        int update = AccessDB.update(sql, actApproval, id);
        return update == 1;
    }

    /**
     * 禁用、启用用户
     *
     * @param id
     * @param status
     * @return
     */
    public static Boolean forbidUser(Integer id, Integer status) {
        String sql = "UPDATE  `registered_users`  set `isForbidden`=? where id=?";
        int update = AccessDB.update(sql, status, id);
        return update == 1;
    }

    /**
     * 查询用户
     *
     * @param name
     * @param realName
     * @return
     */
    public static User queryUser(String name, String realName) {
        StringBuilder stringBuilder = new StringBuilder("select * from  `registered_users` where 1=1");
        if (notBlank(name)) {
            stringBuilder.append("  and userName=" + name);
        }
        if (
                notBlank(realName)) {
            stringBuilder.append("  and realName=" + realName);
        }
        return AccessDB.queryOne(stringBuilder.toString(), User.class);
    }

    private static boolean notBlank(String realName) {
        return null != realName && !("".equals(realName.trim()));
    }

    /**
     * 重置密码
     *
     * @param user
     * @return
     */
    public static Boolean modifyPwd(User user) {
        String sql = "UPDATE  `registered_users`  set `passWord`=? where id=?";
        return 1 == AccessDB.update(sql, user.getPassWord(), user.getID());
    }

    /**
     * 审批报名人员
     *
     * @param userName
     * @param erlApproval
     * @return
     */
    public static Boolean auditEnRolledUser(String userName, Integer erlApproval) {
        String sql = "UPDATE  `enrolled_users`  set `erlApproval`=? where userName=?";
        int update = AccessDB.update(sql, erlApproval, userName);
        return update == 1;
    }

    /**
     * 报名概况
     */
    public static RollCount queryRollCount() {
        String sql = "SELECT\n" +
                "\tcount( 0 ) AS total,\n" +
                "\tsum( IF ( sex = '女', 1, 0 ) ) AS woman,\n" +
                "\tsum( IF ( sex = '男', 1, 0 ) ) AS man,\n" +
                "\tround( rand( ) * 100 ) AS age1,\n" +
                "\tround( rand( ) * 100 ) AS age2,\n" +
                "\tround( rand( ) * 100 ) AS age3\n" +
                "\t\n" +
                "FROM\n" +
                "\tenrolled_users";
        RollCount rollCount = AccessDB.queryOne(sql, RollCount.class);
        return rollCount;
    }

    /**
     * 参赛列表
     */
    public static List<EnrollUser> listEnrollUser() {
        String sql = "select * from enrolled_users ";
        List<EnrollUser> enrollUsers = AccessDB.queryList(sql, EnrollUser.class);
        return enrollUsers;
    }

    /**
     * 公告列表
     */
    public static List<Notice> listNotice() {
        String sql = "select * from anouncement ";
        List<Notice> data = AccessDB.queryList(sql, Notice.class);
        return data;
    }

    /**
     * 新闻列表
     */
    public static List<News> listNews() {
        String sql = "select * from news ";
        List<News> data = AccessDB.queryList(sql, News.class);
        return data;
    }

    /**
     * 赛事报名
     */
    public static Boolean addEnrollUser(EnrollUser param) {
        String sql = "INSERT INTO `enrolled_users`(`userName`, `realName`, `sex`, `idCard`, `applicationTime`) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        return 1 == AccessDB.update(sql, param.getUserName(), param.getRealName(), param.getSex(), param.getIdCard(), param.getIdCard(), new Date());
    }

    /**
     * 查询报名用户
     */
    public static EnrollUser queryEnrollUser(String name, String realName) {
        StringBuilder stringBuilder = new StringBuilder("select * from  `enrolled_users` where 1=1");
        if (notBlank(name)) {
            stringBuilder.append("  and userName=" + name);
        }
        if (notBlank(realName)) {
            stringBuilder.append("  and realName=" + realName);
        }
        stringBuilder.append(" limit 1");
        return AccessDB.queryOne(stringBuilder.toString(), EnrollUser.class, name, realName);
    }


    /**
     * 更新报名信息
     */
    public static Boolean updateEnrolUser(EnrollUser param) {
        String sql = "UPDATE  `enrolled_users` SET `userName` = ?, `realName` =?, `sex` = ?, `idCard` = ?, `erlApproval` = ?, `applicationTime` =? WHERE `userName` =? ";
        return 1 == AccessDB.update(sql, param.getUserName(), param.getRealName(), param.getSex(), param.getIdCard(), param.getIdCard(), new Date(), param.getUserName());
    }

}
